package com.bysj.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bysj.dao.AdvDao;
import com.bysj.entity.Adv;
import com.bysj.util.Dbutils;

public class AdvDaoImpl implements AdvDao{
private Dbutils db=new Dbutils();
	@Override
	public void addAdv(String sql, Adv adv) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, adv.getTitle());
			pst.setString(2, adv.getTime());
			pst.setString(3, adv.getType());
			pst.setString(4, adv.getContent());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void delAdv(String sql, Adv adv) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, adv.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void updateAdv(String sql, Adv adv) {
		
	}

	@Override
	public ArrayList<Adv> queryAllAdv(String sql) {
		return null;
	}

	@Override
	public int countAllAdv(String sql) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		int count=0;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			if(set.next()){
				count=set.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return count;
	}
	@Override
	public Adv queryAdv(String sql,Adv adv) {
		Adv ad=new Adv();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, adv.getId());
			set=pst.executeQuery();
			if(set.next()){
				ad.setId(set.getInt(1));
				ad.setTitle(set.getString(2));
				ad.setType(set.getString(3));
				ad.setTime(set.getString(4));
				ad.setContent(set.getString(5));
				ad.setDocname(set.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return ad;
	}

	@Override
	public ArrayList<Adv> FenyeQueryAdv(String sql, int page, int rows) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		ArrayList<Adv> list=new ArrayList<Adv>();
		Adv adv=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			set=pst.executeQuery();
			while(set.next()){
				adv=new Adv();
				adv.setId(set.getInt(1));
				adv.setTitle(set.getString(2));
				adv.setType(set.getString(3));
				adv.setTime(set.getString(4));
				adv.setContent(set.getString(5));
				adv.setDocname(set.getString(6));
				list.add(adv);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public ArrayList<Adv> queryadvBytype(String sql, Adv adv) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		ArrayList<Adv> list=new ArrayList<Adv>();
		Adv a=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, adv.getType());
			set=pst.executeQuery();
			while(set.next()){
				a=new Adv();
				a.setId(set.getInt(1));
				a.setTitle(set.getString(2));
				a.setType(set.getString(3));
				a.setTime(set.getString(4));
				list.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public void updateAdvDocById(String sql, Adv adv) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, adv.getDocname());
			pst.setInt(2, adv.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
		
	}

	@Override
	public ArrayList<Adv> queryGuideLikeByTitle(String sql) {
		Connection  conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		ArrayList<Adv> list=new ArrayList<Adv>();
		Adv adv=null;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			while(set.next()){
				adv=new Adv();
				adv.setId(set.getInt(1));
				adv.setTitle(set.getString(2));
				adv.setType(set.getString(3));
				adv.setTime(set.getString(4));
				adv.setContent(set.getString(5));
				adv.setDocname(set.getString(6));
				list.add(adv);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

}
